package edu.uoc.tdp.pac4.database;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import javax.swing.JOptionPane;

import edu.uoc.tdp.pac4.beans.Attendance;
import edu.uoc.tdp.pac4.beans.AttendanceStudent;
import edu.uoc.tdp.pac4.beans.Group;
import edu.uoc.tdp.pac4.beans.Registration;
import edu.uoc.tdp.pac4.common.util.LanguageUtils;

public class AttendanceManager {

	Connection con = null;
	private final String driver = "org.postgresql.Driver";
	PreparedStatement pst = null;
	ResultSet rs = null;
	DateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");

	public AttendanceManager() {
		super();
	}

	@Deprecated
	private Connection connBBDD() throws SQLException {
		Connection connect = null;
		try {
			connect = DiskManager.getInstance().getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connect;
	}

	public boolean conectaBD() {

		try {
			Class.forName(driver);
			Properties prop = new Properties();

			String path = getClass().getProtectionDomain().getCodeSource()
					.getLocation().toString().substring(6);
			path = "/" + path.subSequence(0, path.length() - 4).toString();
			java.io.FileInputStream fis = new java.io.FileInputStream(
					new java.io.File(path + "/properties/conf.properties"));
			prop.load(fis);

			String url = prop.getProperty("url");
			String usuario = prop.getProperty("username");
			String clave = prop.getProperty("password");
			con = DriverManager.getConnection(url, usuario, clave);

		} catch (ClassNotFoundException exc) {
			exc.printStackTrace();
			return false;
		} catch (FileNotFoundException exc) {
			exc.printStackTrace();
			return false;
		} catch (IOException exc) {
			exc.printStackTrace();
			return false;
		} catch (SQLException exc) {
			exc.printStackTrace();
			return false;
		} catch (Exception exc) {
			JOptionPane.showMessageDialog(null,
					LanguageUtils.getMessage("client.error.message.generic"),
					LanguageUtils.getMessage("client.error.title"),
					JOptionPane.ERROR_MESSAGE);
			return false;
		}
		return true;
	}

	/**
	 * Obtains all the active attendances for the registrated groups of the
	 * student
	 * 
	 * @param List
	 *            <Registration> regs List the actual accepted regs of the
	 *            student
	 * @return List<Attendance> List of the opened attendances wich the student
	 *         can check in
	 */
	public List<Attendance> getAttendancesByStudent(List<Registration> regs) {

		List<Attendance> atts = new ArrayList<Attendance>();
		try {

			if (!regs.isEmpty()) {
				for (Iterator<Registration> it = regs.iterator(); it.hasNext();) {
					try {
						Registration r = it.next();
						String q = "select * from asistencia where grupoid = ? and horafin is null";
						con = DiskManager.getInstance().getConnection();
						pst = con.prepareStatement(q);
						pst.setInt(1, r.getGroupId());
						rs = pst.executeQuery();
						if (rs.next()) {
							Attendance att = new Attendance(
									rs.getInt("asistid"),
									rs.getDate("fechaasist"),
									rs.getTime("horainicio"),
									rs.getInt("grupoid"));
							atts.add(att);
						}

					} catch (SQLException e) {
						e.printStackTrace();
					} finally {
						try {
							if (rs != null)
								rs.close();
							if (pst != null)
								pst.close();
							if (con != null)
								con.close();
						} catch (SQLException e) {
							e.printStackTrace();
						} catch (Exception e) {
							e.printStackTrace();
						}

					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return atts;
	}

	public boolean checkInAssistanceStudent(AttendanceStudent attstd) {

		try {

			String q = "SELECT * FROM asistenciaalumno WHERE asistid = ?";

			con = DiskManager.getInstance().getConnection();
			pst = con.prepareStatement(q);
			pst.setInt(1, attstd.getAssistenciaId());
			rs = pst.executeQuery();
			if (!rs.next()) {

				q = "INSERT INTO asistenciaalumno(asistid, alumnoid) "
						+ "VALUES(?, ?)";

				pst = con.prepareStatement(q);
				pst.setInt(1, attstd.getAssistenciaId());
				pst.setString(2, attstd.getAlumneId());
				pst.executeUpdate();

				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	/**
	 * Search an attendance by id
	 * 
	 * @param asistenciaid
	 */

	public Attendance getAttendance(int attId) {
		try {
			String q = "SELECT * FROM asistencia WHERE asistid = ?";

			con = DiskManager.getInstance().getConnection();
			pst = con.prepareStatement(q);
			pst.setInt(1, attId);
			rs = pst.executeQuery();
			if (rs.next()) {
				Attendance att = new Attendance(rs.getInt("asistid"),
						rs.getDate("fechaasist"), rs.getTime("horainicio"),
						rs.getInt("grupoid"));
				return att;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * Save a new attendance inserting the group, date and starting time.
	 * 
	 * @param group
	 * @return
	 */
	public Attendance saveAttendance(Group group) {

		java.util.Date date = new java.util.Date();

		java.sql.Time time = new java.sql.Time(date.getTime());
		java.sql.Date sqlToday = new java.sql.Date(date.getTime());

		try {
			con = connBBDD();

			String querystring = "INSERT INTO asistencia(grupoid, fechaasist, horainicio) "
					+ "VALUES(?, ?, ?)";

			con.prepareStatement(querystring);
			pst = con.prepareStatement(querystring);
			pst.setInt(1, group.getGroupId());
			pst.setDate(2, sqlToday);
			pst.setTime(3, time);
			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		Attendance attendance = findAttendance(group.getGroupId(), time, date);
		return attendance;

	}

	/**
	 * Finds the attendance that involved a particular group at a particular
	 * time
	 * 
	 * @param groupId
	 * @param time
	 * @param date
	 * @return
	 */
	public Attendance findAttendance(int groupId, Time time, java.util.Date date) {

		Attendance attendance = null;
		try {
			String querystring = "SELECT asistid, fechaasist, horainicio, grupoid FROM ASISTENCIA  WHERE grupoid = ?  AND horainicio=?  "; // AND
																																			// fechaasist=?
																																			// ";
			con = connBBDD();
			pst = con.prepareStatement(querystring);
			pst.setInt(1, groupId);
			pst.setTime(2, time);
			rs = pst.executeQuery();

			if (rs.next()) {
				attendance = new Attendance();
				attendance.setAttendanceId(rs.getInt(1));
				attendance.setAttendanceDate(rs.getDate(2));
				attendance.setStartTime(rs.getDate(3));
				attendance.setGroupid(rs.getInt(4));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return attendance;
	}

	/**
	 * Closes the attendance updating the final time.
	 * 
	 * @param attendance
	 */
	public void closeAttendance(Attendance attendance) {
		java.util.Date date = new java.util.Date();
		java.sql.Time time = new java.sql.Time(date.getTime());

		try {
			String querystring = "UPDATE asistencia SET horafin=? WHERE asistid=? ";
			con = connBBDD();
			pst = con.prepareStatement(querystring);

			pst.setTime(1, time);
			pst.setInt(2, attendance.getAttendanceId());
			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * Counts the number of students present in the group
	 * 
	 * @param attendance
	 * @return
	 */
	public int countPresents(Attendance attendance) {

		AttendanceStudent astudent = null;
		int presents = 0;
		try {
			String querystring = "SELECT *  FROM asistenciaalumno WHERE asistid = ?  ";
			con = connBBDD();

			pst = con.prepareStatement(querystring);
			pst.setInt(1, attendance.getAttendanceId());
			rs = pst.executeQuery();
			while (rs.next()) {
				presents = presents + 1;

				astudent = new AttendanceStudent();
				astudent.setAssistAlumnId(rs.getInt(1));
				System.out.println(astudent.getAssistAlumnId());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return presents;

	}

	/**
	 * Updates the attendance with the info of present and missing students
	 * 
	 * @param presents
	 * @param absents
	 * @param attendance
	 */
	public void updateAttendance(int presents, int absents,
			Attendance attendance) {

		try {
			con = connBBDD();

			String querystring = "UPDATE  asistencia SET totalasisten=?, totalnoasiten=? where asistid = ?";

			con = connBBDD();
			pst = con.prepareStatement(querystring);
			pst.setInt(1, presents);
			pst.setInt(2, absents);
			pst.setInt(3, attendance.getAttendanceId());
			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}
}
